import io
import time

import pandas as pd
import xlsxwriter as excel_writer
from flask import Flask, render_template, make_response

app = Flask(__name__)


@app.route('/')
def hello_world():
    return render_template('index.html')


@app.route('/down_excel1/<beginDate>/<endDate>')
def down_excel1(beginDate, endDate):
    print("入参：%s %s" % (beginDate, endDate))
    sio = io.BytesIO()
    workbook = excel_writer.Workbook(sio)  # 直接写到io中
    sheet = workbook.add_worksheet(u'sheet1')
    style = workbook.add_format()  # 设置风格
    sheet.merge_range(0, 0, 0, 5, 'aaa')  # 合并单元格
    sheet.write('A2', u'内容', style)
    for i in range(2, 20):
        sheet.write(i, 2, 1)
    sheet.write(11, 2, '=SUM(1:20)')  # 增加公式
    sheet.set_column(0, 5, 5)  # 设置列宽
    sheet.set_default_row(15)  # 设置默认行高
    workbook.close()  # 需要关闭
    sio.seek(0)  # 找到流的起始位置
    resp = make_response(sio.getvalue())
    resp.headers["Content-Disposition"] = "attachment; filename={}.xlsx".format('name')
    resp.headers['Content-Type'] = 'application/x-xlsx'
    return resp


@app.route('/down_excel2/<beginDate>/<endDate>')
def down_excel2(beginDate, endDate):
    print("入参：%s %s" % (beginDate, endDate))
    sio = io.BytesIO()
    workbook = excel_writer.Workbook(sio)  # 直接写到io中
    sheet = workbook.add_worksheet(u'sheet1')
    style = workbook.add_format()  # 设置风格
    sheet.merge_range(0, 0, 0, 5, 'aaa')  # 合并单元格
    sheet.write('A2', u'内容', style)
    for i in range(2, 10):
        sheet.write(i, 2, 1)
    sheet.write(11, 2, '=SUM(1:10)')  # 增加公式
    sheet.set_column(0, 5, 10)  # 设置列宽
    sheet.set_default_row(35)  # 设置默认行高
    workbook.close()  # 需要关闭
    sio.seek(0)  # 找到流的起始位置
    resp = make_response(sio.getvalue())
    resp.headers["Content-Disposition"] = "attachment; filename={}.xlsx".format('name')
    resp.headers['Content-Type'] = 'application/x-xlsx'
    return resp


@app.route('/down_excel3/<beginDate>/<endDate>')
def down_excel3(beginDate, endDate):
    print("入参：%s %s" % (beginDate, endDate))
    sheet_1 = pd.DataFrame([{'姓名': '张三', '职业': '搬砖工'}, {'姓名': '李四', '职业': '科学家'}, {'姓名': '王五', '职业': '文学家'}])
    sheet_2 = pd.DataFrame([{'姓名': '张三', '爱好': '搬砖工'}, {'姓名': '李四', '爱好': '科学家'}, {'姓名': '王五', '爱好': '文学家'}])
    out = io.BytesIO()
    writer = pd.ExcelWriter(out, engine='xlsxwriter')
    sheet_1.to_excel(excel_writer=writer, index=False, sheet_name='示例1')
    sheet_2.to_excel(excel_writer=writer, index=False, sheet_name='示例2')
    writer.save()
    file_name = time.strftime('%Y-%m-%d', time.localtime(time.time())) + '.xlsx'
    resp = make_response(out.getvalue())
    resp.headers["Content-Disposition"] = "attachment; filename=%s" % file_name
    resp.headers["Content-type"] = 'application/x-xlsx'
    return resp


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=9988, debug=True)
